package edu.mju.dao.impl;

import edu.mju.dao.ICompanyDao;
import edu.mju.entity.TouristCompany;
import edu.mju.entity.User;
import edu.mju.util.Pager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author:wyp
 * @Date:2020/5/24 9:44
 */
public class CompanyDaoImpl implements ICompanyDao {
    Connection conn = null;
    PreparedStatement preparedStatement = null ;

    public PreparedStatement preparedStatement(String sql){
        try {
            conn  = MySqlDbUtil.getConnection();
            preparedStatement = conn.prepareStatement(sql);
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("CompanyDaoImpl>preparedStatement方法出错");
        }
        return preparedStatement;
    }

    @Override
    public TouristCompany findByCompanyId(Integer companyId) {
        TouristCompany touristCompany = new TouristCompany();
        try {
            Connection conn = MySqlDbUtil.getConnection();
            String sql = "SELECT * FROM TouristCompany where companyId=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,companyId);
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()){
                touristCompany.setUserId(rs.getInt("userId"));
                touristCompany.setCompanyName(rs.getString("companyName"));
                touristCompany.setCompanyId(rs.getInt("companyId"));
            }
            rs.close();
            pstmt.close();
            conn.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return touristCompany;
    }

    @Override
    public TouristCompany findById(Integer id) {
        TouristCompany touristCompany = new TouristCompany();
        try {
            Connection conn = MySqlDbUtil.getConnection();
            String sql = "SELECT * FROM TouristCompany where userId=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()){
                touristCompany.setUserId(rs.getInt("userId"));
                touristCompany.setCompanyName(rs.getString("companyName"));
                touristCompany.setCompanyId(rs.getInt("companyId"));
            }
            rs.close();
            pstmt.close();
            conn.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return touristCompany;
    }

    /**
     *  添加公司方法
     * @param company
     * @return 插入成功为：true ，否则为：false 返回给CompanyController.companyAdd();
     */
    @Override
    public boolean insert(TouristCompany company) {
        boolean insert= false;
        try {
            String sql = "INSERT INTO TouristCompany(companyName,userId) VALUES (?,?)";
            PreparedStatement pstmt = preparedStatement(sql);
            if(pstmt != null){
                pstmt.setString(1,company.getCompanyName());
                pstmt.setInt(2,company.getUserId());
                int result =pstmt.executeUpdate();
                if(result>0){
                    insert = true;
                }
            }
            pstmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return insert;
    }

    /**
     * 删除公司操作
     * @param id 获取公司的ID
     * @return true删除成功。
     */
    @Override
    public boolean delete(Integer id) {
        boolean delete = false;
        try {
            String sql = "DELETE FROM TouristCompany WHERE companyId=?";
            PreparedStatement pstmt = preparedStatement(sql);
            pstmt.setInt(1,id);
            int result =pstmt.executeUpdate();
            if(result>0){
                delete = true;
            }
            pstmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return delete;
    }

    /**
     * 根据companyID 更新公司名
     * @param company
     * @return 返回给CompanyController.companyEdit更新操作
     */
    @Override
    public boolean update(TouristCompany company) {
        boolean update = false;
        try {
            String sql = "UPDATE TouristCompany SET companyName=? WHERE companyId=?";
            PreparedStatement pstmt = preparedStatement(sql);
            pstmt.setString(1,company.getCompanyName());
            pstmt.setInt(2,company.getCompanyId());
            int result =pstmt.executeUpdate();
            if(result>0){
                update = true;
            }
            pstmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return update;
    }

    @Override
    public Pager pagenate(int page, int pageSize, TouristCompany company) {
        Pager pager = new Pager(page,pageSize);
        String sql = "select count(*) as total from TouristCompany ";
        StringBuilder where = new StringBuilder("where 1=1 ");
        List<Object> params = new ArrayList<>();
        if(company != null) {
            if (company.getCompanyName()!= null && !"".equals(company.getCompanyName())) {
                where.append("AND companyName like ? ");
                params.add("%" + company.getCompanyName() + "%");
            }
        }
        try {
            Connection conn = MySqlDbUtil.getConnection();
            PreparedStatement pstmt = conn.prepareStatement(sql + where.toString());
            for(int i = 0;i<params.size();i++){
                pstmt.setObject(i+1,params.get(i));
            }
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()){
                pager.setRows(rs.getInt("total"));
            }

            rs.close();;
            pstmt.close();
            //这里要注意sql 语句之间的空格
            sql = " select * from TouristCompany ";
            pstmt = conn.prepareStatement(sql+where.toString()+" limit ?,? ");
            int index = 0;
            for(int i = 0;i<params.size();i++){
                ++index;
                pstmt.setObject(i+1,params.get(i));
            }
            pstmt.setObject(++index,(pager.getPage()-1)*pager.getPageSize());
            pstmt.setInt(++index,pager.getPageSize());
            rs =pstmt.executeQuery();
            List<Object> data = new ArrayList<>();
            while (rs.next()){
                company = new TouristCompany(rs.getInt("companyId"),rs.getString("companyName"),
                        rs.getInt("userId"));
                data.add(company);
            }
            pager.setData(data);
            rs.close();
            pstmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return pager;
    }
}
